package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;

import constants.Constants;
import domain.file.FileDetails;

public class FileDao {
	String dbUrl = Constants.DB_URL;
	String dbUsername = Constants.DB_USERNAME;
	String dbPassword = Constants.DB_PASSWORD;
	
	public ArrayList<FileDetails> getFileList(int userId) {
		ArrayList<FileDetails> result = new ArrayList<FileDetails>();
		String query = "select File_No, File_Name, File_Desc, Analyzed from dbo.DVIP_SYS_FILES where User_Id = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		ResultSet  rs = null;
		PreparedStatement pStmt = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			pStmt.setInt(1, userId);
			rs = pStmt.executeQuery();
			while(rs.next()){
				FileDetails file = new FileDetails();
				
				file.setFileNo(rs.getInt("File_No"));
				file.setUserId(userId);
				file.setFileName(rs.getString("File_Name"));
				file.setFileDesc(rs.getString("File_Desc"));
				file.setAnalyzed(rs.getInt("Analyzed"));
				
				result.add(file);
			}
			
			rs.close();
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public boolean deleteFile(int fileNo){
		boolean result = true;
		
		Connection sqlConnection = null;
		CallableStatement proc = null;
	    
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			proc = sqlConnection.prepareCall("{ call dbo.Delete_scenario(?) }");
			
			proc.setInt(1, fileNo);
		    proc.execute();
		    
			proc.close();
			sqlConnection.close();
		} catch (ClassNotFoundException e1) {
			result = false;
			e1.printStackTrace();
		} catch (SQLException e) {
			result = false;
			e.printStackTrace();
		}
		
		return result;
	}
	
	public int createNewFile(int userId, String fileName, String fileDesc, int defaultFileNo){
		int newFileNo = -1;
		
		Connection sqlConnection = null;
		CallableStatement proc = null;
	    
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			proc = sqlConnection.prepareCall("{ call dbo.Create_new_scenario(?, ?, ?, ?, ?) }");
			
			proc.setInt(1, userId);
		    proc.setString(2, fileName);
		    proc.setString(3, fileDesc);
		    proc.setInt(4, defaultFileNo);
		    proc.registerOutParameter(5, Types.INTEGER);
		    
		    proc.execute();
		    newFileNo = proc.getInt(5);
		    
			proc.close();
			sqlConnection.close();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return newFileNo;
	}
	
	public FileDetails getDefaultFile(){
		FileDetails result = new FileDetails();
		String query = "select File_No, User_Id, File_Name, File_Desc, Analyzed from dbo.DVIP_SYS_FILES where File_No = (select File_No from dbo.DVIP_SYS_DEFAULT_FILE)";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		ResultSet  rs = null;
		PreparedStatement pStmt = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			rs = pStmt.executeQuery();
			while(rs.next()){
				result.setFileNo(rs.getInt("File_No"));
				result.setUserId(rs.getInt("User_Id"));
				result.setFileName(rs.getString("File_Name"));
				result.setFileDesc(rs.getString("File_Desc"));
				result.setAnalyzed(rs.getInt("Analyzed"));
			}
			
			rs.close();
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	
	public boolean updateFileStatus(int fileNo, int newStatus) {
		boolean result = true;
		
		if(newStatus!=Constants.ANALYZED && newStatus!=Constants.NOT_ANALYZED){
			return false;
		}
		
		String query = "update dbo.DVIP_SYS_FILES set Analyzed = ?, Analyzed_Time = ? where File_No = ?";
		
		Connection sqlConnection = null;
		PreparedStatement pStmt = null;
		
		int colIndex = 1;
		
		java.sql.Timestamp sqlTimeStamp = new java.sql.Timestamp(new java.util.Date().getTime());
		
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			
			pStmt.setInt(colIndex++, newStatus);
			pStmt.setTimestamp(colIndex++, sqlTimeStamp);
			pStmt.setInt(colIndex++, fileNo);
			
			pStmt.executeUpdate();
			
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			result = false;
			e.printStackTrace();
		} catch (ClassNotFoundException e1) {
			result = false;
			e1.printStackTrace();
		}
		
		return result;
	}
}
